<?php
/**
 * Created by PhpStorm.
 * User: lingyun
 * Date: 2018/6/17
 * Time: 10:26
 * Desc: 工厂订单
 */
namespace app\kuguan\controller;
use controller\BasicAdmin;
use service\DataService;
use think\Db;
use PHPExcel;
use PHPExcel_IOFactory;

class Gongchang extends BasicAdmin{
    /**
     * @Author  lingyun
     * @Desc    工厂订单
     * 2018-06-17
     */
    public function index(){
        $get = input('get.');
        $map['b.status'] = ['in','1,3'];
        $map['b.is_diannei'] = ['eq',0];
        if(!empty($get['date'])){
            list($start, $end) = explode('-', str_replace(' ', '', $get['date']));
            $start = $start."  00:00:00";		$end = $end." 23:59:59";
            $start = strtotime($start);			$end = strtotime($end);
            $map['b.addtime'] = array(array('gt',$start),array('lt',$end),'AND');
            $result = db('store_order_goods')->alias('a')->join('ys_store_order b','a.order_id=b.id','LEFT')->join('ys_goods c','a.goods_id=c.id','LEFT')->join('ys_goods_spec_detail d','a.goods_spec_id=d.id','LEFT')->where($map)->field('c.name,d.title,sum(0+a.del_num) as del_num,sum(0+a.chengben) as chengben,sum(0+a.sale_price) as sale_price,sum(0+a.dz_chengben) as dz_chengben')->group('goods_spec_id')->select();
        }else{
            $time = $this->get_month_time();
            $map['a.addtime'] = array(array('gt',$time['start']),array('lt',$time['end']),'AND');
            $result = db('store_order_goods')->alias('a')->join('ys_store_order b','a.order_id=b.id','LEFT')->join('ys_goods c','a.goods_id=c.id','LEFT')->join('ys_goods_spec_detail d','a.goods_spec_id=d.id','LEFT')->where($map)->field('c.name,d.title,sum(0+a.del_num) as del_num,sum(0+a.chengben) as chengben,sum(0+a.sale_price) as sale_price,sum(0+a.dz_chengben) as dz_chengben')->group('goods_spec_id')->select();
        }

        $this->assign('title','工厂售卖商品');
        $this->assign('result',$result);

        return $this->fetch();
    }

    /**
     * @Author  lingyun
     * @Desc    获取本月时间
     * @return array
     */
    private function get_month_time(){
        $begin_time = strtotime(date("Y-m-d H:i:s", mktime ( 0, 0, 0, date ( "m" ), 1, date ( "Y" ) ) ));
        $end_time = strtotime(date("Y-m-d H:i:s", mktime ( 23, 59, 59, date ( "m" ), date ( "t" ), date ( "Y" ) ) ));

        return ['start'=>$begin_time,'end'=>$end_time];
    }

    /**
     * @Author  lingyun
     * @Desc    导出信息
     * 2018-06-17
     */
    public function export(){
        $get = input('get.');
        $map['b.status'] = ['in','1,3'];
        $map['b.is_diannei'] = ['eq',0];
        if(!empty($get['date'])){
            list($start, $end) = explode('-', str_replace(' ', '', $get['date']));
            $start = $start."  00:00:00";		$end = $end." 23:59:59";
            $start = strtotime($start);			$end = strtotime($end);
            $map['b.addtime'] = array(array('gt',$start),array('lt',$end),'AND');
            $result = db('store_order_goods')->alias('a')->join('ys_store_order b','a.order_id=b.id','LEFT')->join('ys_goods c','a.goods_id=c.id','LEFT')->join('ys_goods_spec_detail d','a.goods_spec_id=d.id','LEFT')->where($map)->field('c.name,d.title,sum(0+a.del_num) as del_num,sum(0+a.chengben) as chengben,sum(0+a.sale_price) as sale_price,sum(0+a.dz_chengben) as dz_chengben')->group('goods_spec_id')->select();
        }else{
            $time = $this->get_month_time();
            $map['a.addtime'] = array(array('gt',$time['start']),array('lt',$time['end']),'AND');
            $result = db('store_order_goods')->alias('a')->join('ys_store_order b','a.order_id=b.id','LEFT')->join('ys_goods c','a.goods_id=c.id','LEFT')->join('ys_goods_spec_detail d','a.goods_spec_id=d.id','LEFT')->where($map)->field('c.name,d.title,sum(0+a.del_num) as del_num,sum(0+a.chengben) as chengben,sum(0+a.sale_price) as sale_price,sum(0+a.dz_chengben) as dz_chengben')->group('goods_spec_id')->select();
        }

        $filename = '工厂销售详情'.date('YmdHis').'.xls';

        $objPHPExcel = new PHPExcel();//新建一个表
        $objSheet = $objPHPExcel->getActiveSheet();//获得当前活动Sheet
        $objSheet->setTitle("工厂销售详情");
        $title = '工厂销售详情 - '.$get['date'];

        $objSheet->setCellValue('A1', $title);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');      //合并
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(     //加粗和居中
            array(
                'font' => array (
                    'bold' => true
                ),
                'alignment' => array(
                    'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                )
            )
        );


        $objSheet
            ->setCellValue('A2', '商品')
            ->setCellValue('B2', '属性')
            ->setCellValue('C2', '售卖数量')
            ->setCellValue('D2', '成本价')
            ->setCellValue('E2', '销售价')
            ->setCellValue('F2', '对账');

        $j = 3;     //从第三行开始
        foreach ($result as $k => $v) {
            if(empty($v['title'])){
                $title = '无属性';
            }else{
                $title = $v['title'];
            }

            if(empty($v['dz_chengben'])){
                $dz_price = '暂未对账';
            }else{
                $dz_price = $v['dz_chengben'];
            }

            $objSheet
                ->setCellValue('A' . $j, $v['name'])
                ->setCellValue('B' . $j, $title)
                ->setCellValue('C' . $j, $v['del_num'])
                ->setCellValue('D' . $j, $v['chengben'])
                ->setCellValue('E' . $j, $v['sale_price'])
                ->setCellValue('F' . $j, $dz_price);
            $j++;
        }

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $objWriter->save("php://output");
    }

    /**
     * @Author  lingyun
     * @Desc    售卖商品
     * 2018-06-17
     */
    public function salegoods(){
        $get = input('get.');

        $store_id = db('store')->where(['is_deleted'=>0,'is_jiameng'=>0])->value('id');

        if(empty($get['date'])){
            $time = $this->get_month_time();            //本月统计
            $map['b.addtime'] = array(array('gt',$time['start']),array('lt',$time['end']),'AND');
            $map['b.pay_status'] = ['eq',1];
            $map['b.order_status'] = ['in','1,2,3,4'];
            $map['b.store_id'] = ['in',$store_id];

            $join = [
                ['ys_order b','a.order_id=b.id','LEFT'],
                ['ys_goods c','a.goods_id=c.id','LEFT'],
                ['ys_goods_spec_detail d','a.spec_id=d.id','LEFT']
            ];

            $db = db('order_details')->alias('a')->join($join)->where($map)->field('d.price,c.good_number,c.name,d.title,sum(0+a.final_amount) as final_amount,sum(0+a.goods_num) as goods_num,sum((0+a.all_discount_prcie)*(0+a.goods_num)) as discount,sum(0+a.chengben_price) as chengben,sum(0+a.final_amount)-sum(0+a.chengben_price) as profit')->group('a.spec_id');

            $result = db('order_details')->alias('a')->join($join)->where($map)->field('d.price,c.good_number,c.name,d.title,sum(0+a.final_amount) as final_amount,sum(0+a.goods_num) as goods_num,sum((0+a.all_discount_prcie)*(0+a.goods_num)) as discount,sum(0+a.chengben_price) as chengben,sum(0+a.final_amount)-sum(0+a.chengben_price) as profit')->group('a.spec_id')->select();

            $chengben = array_sum(array_map(create_function('$val', 'return $val["chengben"];'), $result));
            $num = array_sum(array_map(create_function('$val', 'return $val["goods_num"];'), $result));
            $profit = array_sum(array_map(create_function('$val', 'return $val["profit"];'), $result));

            $this->assign('title','商品销售统计');
            $this->assign('chengben',$chengben);
            $this->assign('num',$num);
            $this->assign('profit',$profit);


            return parent::_list($db);

        }else{
            $date = input('date');
            list($start, $end) = explode('-', str_replace(' ', '', $date));
            $start = $start."  00:00:00";		$end = $end." 23:59:59";
            $start = strtotime($start);			$end = strtotime($end);
            $time['start'] = $start;
            $time['end'] = $end;

            $map['b.addtime'] = array(array('gt',$time['start']),array('lt',$time['end']),'AND');
            $map['b.pay_status'] = ['eq',1];
            $map['b.order_status'] = ['in','1,2,3,4'];
            $map['b.store_id'] = ['in',$store_id];

            $join = [
                ['ys_order b','a.order_id=b.id','LEFT'],
                ['ys_goods c','a.goods_id=c.id','LEFT'],
                ['ys_goods_spec_detail d','a.spec_id=d.id','LEFT']
            ];

            $db = db('order_details')->alias('a')->join($join)->where($map)->field('c.good_number,c.name,d.title,sum(0+a.final_amount) as final_amount,sum(0+a.goods_num) as goods_num,sum((0+a.all_discount_prcie)*(0+a.goods_num)) as discount,sum(0+a.chengben_price) as chengben,sum(0+a.final_amount)-sum(0+a.chengben_price) as profit')->group('a.spec_id');

            $result = db('order_details')->alias('a')->join($join)->where($map)->field('d.price,c.good_number,c.name,d.title,sum(0+a.final_amount) as final_amount,sum(0+a.goods_num) as goods_num,sum((0+a.all_discount_prcie)*(0+a.goods_num)) as discount,sum(0+a.chengben_price) as chengben,sum(0+a.final_amount)-sum(0+a.chengben_price) as profit')->group('a.spec_id')->select();

            $chengben = array_sum(array_map(create_function('$val', 'return $val["chengben"];'), $result));
            $num = array_sum(array_map(create_function('$val', 'return $val["goods_num"];'), $result));
            $profit = array_sum(array_map(create_function('$val', 'return $val["profit"];'), $result));

            $this->assign('title','商品销售统计');
            $this->assign('chengben',$chengben);
            $this->assign('num',$num);
            $this->assign('profit',$profit);


            return parent::_list($db);
        }
    }

    /**
     * @Author  lingyun
     * @Desc    导出售卖商品信息
     * 2018-06-17
     */
    public function export_salegoods(){
        $get = input('get.');

        $store_id = db('store')->where(['is_deleted'=>0,'is_jiameng'=>0])->value('id');

        if(empty($get['date'])){
            $time = $this->get_month_time();            //本月统计
            $map['b.addtime'] = array(array('gt',$time['start']),array('lt',$time['end']),'AND');
            $map['b.pay_status'] = ['eq',1];
            $map['b.order_status'] = ['in','1,2,3,4'];
            $map['b.store_id'] = ['in',$store_id];

            $join = [
                ['ys_order b','a.order_id=b.id','LEFT'],
                ['ys_goods c','a.goods_id=c.id','LEFT'],
                ['ys_goods_spec_detail d','a.spec_id=d.id','LEFT']
            ];

            $db = db('order_details')->alias('a')->join($join)->where($map)->field('c.good_number,c.name,d.title,sum(0+a.final_amount) as final_amount,sum(0+a.goods_num) as goods_num,sum(0+a.all_discount_prcie)*sum(0+a.goods_num) as discount,sum(0+a.chengben_price) as chengben,sum(0+a.final_amount)-sum(0+a.chengben_price) as profit')->group('a.spec_id')->select();
        }else{
            $date = input('date');
            list($start, $end) = explode('-', str_replace(' ', '', $date));
            $start = $start."  00:00:00";		$end = $end." 23:59:59";
            $start = strtotime($start);			$end = strtotime($end);
            $time['start'] = $start;
            $time['end'] = $end;

            $map['b.addtime'] = array(array('gt',$time['start']),array('lt',$time['end']),'AND');
            $map['b.pay_status'] = ['eq',1];
            $map['b.order_status'] = ['in','1,2,3,4'];
            $map['b.store_id'] = ['in',$store_id];

            $join = [
                ['ys_order b','a.order_id=b.id','LEFT'],
                ['ys_goods c','a.goods_id=c.id','LEFT'],
                ['ys_goods_spec_detail d','a.spec_id=d.id','LEFT']
            ];

            $db = db('order_details')->alias('a')->join($join)->where($map)->field('c.good_number,c.name,d.title,sum(0+a.final_amount) as final_amount,sum(0+a.goods_num) as goods_num,sum(0+a.all_discount_prcie)*sum(0+a.goods_num) as discount,sum(0+a.chengben_price) as chengben,sum(0+a.final_amount)-sum(0+a.chengben_price) as profit')->group('a.spec_id')->select();

        }
        $filename = '售卖商品'.date('YmdHis').'.xls';

        $objPHPExcel = new PHPExcel();//新建一个表
        $objSheet = $objPHPExcel->getActiveSheet();//获得当前活动Sheet
        $objSheet->setTitle("售卖商品");
        $title = '售卖商品 - '.$get['date'];

        $objSheet->setCellValue('A1', $title);
        $objPHPExcel->getActiveSheet()->mergeCells('A1:G1');      //合并
        $objPHPExcel->getActiveSheet()->getStyle('A1')->applyFromArray(     //加粗和居中
            array(
                'font' => array (
                    'bold' => true
                ),
                'alignment' => array(
                    'horizontal' => \PHPExcel_Style_Alignment::HORIZONTAL_CENTER
                )
            )
        );


        $objSheet
            ->setCellValue('A2', '商品')
            ->setCellValue('B2', '商品属性')
            ->setCellValue('C2', '商品数量')
            ->setCellValue('D2', '交易总价')
            ->setCellValue('E2', '优惠')
            ->setCellValue('F2', '成本')
            ->setCellValue('G2', '利润');

        $j = 3;     //从第三行开始
        foreach ($db as $k => $v) {
            $objSheet
                ->setCellValue('A' . $j, $v['name'])
                ->setCellValue('B' . $j, $v['title'])
                ->setCellValue('C' . $j, $v['goods_num'])
                ->setCellValue('D' . $j, $v['final_amount'])
                ->setCellValue('E' . $j, $v['discount'])
                ->setCellValue('F' . $j, $v['chengben'])
                ->setCellValue('G' . $j, $v['profit']);
            $j++;
        }

        $objWriter = PHPExcel_IOFactory::createWriter($objPHPExcel, 'Excel5');
        header('Content-Type: application/vnd.ms-excel');
        header('Content-Disposition: attachment;filename="' . $filename . '"');
        header('Cache-Control: max-age=0');

        $objWriter->save("php://output");
    }


}